
import java.sql.*;


public class JavaDB_Test {

	
	static String[] category = {"EAT OUT", "GAS", "GROCERY", "TRANSPORTATION", "MORTGAGE", "MAINTENANCE","MISCELLANEOUS","INSURANCE","UTILITY", "HYDRO"};
	static String[] transactions = {"COSTCO", "PETRO CANADA", "ESSO" , "MILESTONE", "KEG STEAK HOUSE", "CIBC", "TD", "TTC", "SUSHI RESTAURANT", "FOOD BASICS", "NO FRILLS", "KENZO", "CANADIAN TIRE"};
	static String[] trans_method= {"CASH", "VISA CARD", "CASH","CASH", "VISA CARD", "CASH", "VISA CARD", "VISA CARD", "CHEQUE"};
	static String[] year = {"2012"};
	static String[] month ={"01","02","03","04","05","06","07","08","09","10","11","12"};
	static String[] day= {"01","02","03","07","08","12","11","19","13","14","22","27","23","28","26","24"};
	static String[] user= {"BORA","SHAWN","FAMILY"};
	
	public static void main(String[] args){
		String dbName = "C:\\ECLIPSE\\JAVA-EXERCISES\\MoneyKeeper\\bin\\DB";
		String driver = "org.apache.derby.jdbc.EmbeddedDriver";
		String protocol = "jdbc:derby:";
		String connectionURL = protocol + dbName;
		try{
			Class.forName(driver).newInstance();
			System.out.println("Loaded the appropriate driver.");
			
			Connection conn = null;
			conn = DriverManager.getConnection(connectionURL);
			System.out.println("Connected to and created database derbyDB");
			conn.setAutoCommit(false);
			
			Statement s = conn.createStatement();
			
			String result;
			int in_out = 0;
			for (int i = 0; i < 40; i++){
				result = "";
				
					

					result = "INSERT INTO MoneyTable (USER_NAME, TRANS_DATE, CATEGORY, TRANSACTIONS, TRANS_METHOD, INCOME,EXPENSE) VALUES (" 
							+"'" + getRandArr(user) +"' "
							+ ", '" + getRandArr(year) +"-" + getRandArr(month) + "-" + getRandArr(day) +"' "
							+ ", '" + getRandArr(category) +"'" 
							+ ", '" + getRandArr(transactions) +"' "
							+ ", '" + getRandArr(trans_method) +"' "
							+ ((in_out==1)?", " + (int)(Math.random()*200 + 10)  : ", 0")  
							+ ((in_out==0)?", " + (int)(Math.random()*200 + 10) : ", 0") 
							+ ")";
					System.out.println(result);
					s.execute(result);
					
					
			}
			//s.execute("INSERT INTO MoneyTable (TRANS_DATE,CATEGORY,TRANSACTIONS,TRANS_METHOD, INCOME,EXPENSE,MEMO) Values ('2001-11-09','GAS','PETRO CANADA', 'VISA' ,null,77, 'FUEL' )");
			
			
			
			ResultSet rs = s.executeQuery("select * from MoneyTable");
			/*while(rs.next()){
				System.out.print(rs.getDate(1) +   "  ");
				System.out.print(rs.getString(2) +   "  ");
				System.out.print(rs.getString(3) +   "  ");
				System.out.print(rs.getString(4) +   "  ");
				System.out.print(rs.getFloat(5) +   "  ");
				System.out.print(rs.getFloat(6) +   "  ");
				System.out.println(rs.getString(7));
			}*/
			
			
			
			
			rs.close();
			s.close();
			
			conn.commit();
	        conn.close();
	        
	        boolean gotSQLExc = false;
	        
	        try
	             {
	                 DriverManager.getConnection("jdbc:derby:;shutdown=true");
	             }
	             catch (SQLException se)
	             {
	                 gotSQLExc = true;
	             }

	            if (!gotSQLExc)
	             {
	                 System.out.println("Database did not shut down normally");
	             }
	             else
	             {
	                 System.out.println("Database shut down normally");
	             }
		}catch(Exception e){
			e.printStackTrace();
		}
	}
		


        
	public static String getRandArr(String[] arr){
		return arr[getRand(arr.length-1)];
	}
	public static int getRand(int n){
		return getRand(0,n);
	}
	public static int getRand(int from, int to){
		return (int)(Math.random() * (Math.abs(to-from)+1))+Math.min(from, to);
	}
}
